﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-23
修改:
修改时间: 
功能: 推台配置表
 ******************************************************/

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using com.nm.library.Helper;
using System.ComponentModel;
using VerticaAPI;
using Nancy.Extensions;
using com.nm.library;

namespace PlanAPI
{
    [Description("推台配置表")]
    public class PLBasTableConfHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：梁家健
        /// Create Date：2017-03-23
        [MustLogin]
        [Description("推台配置表")]
        [ReturnType(typeof(PaperData<PLBasTableConfModel.ListModel>))]
        public ApiResult GetPLBasTableConfList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                var count = ReturnPLBasTableConfCount(ParentAreaID);
                var result = PaperData<PLBasTableConfModel.ListModel>.Create(count, Size, Page);
                var dt = ReturnPLBasTableConfList(ParentAreaID, Page, Size);
                var list = ModelConvertHelper<PLBasTableConfModel.ListModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回推台配置表列表
        /// </summary>
        /// <param name="keys"></param>
        /// <param name="page"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-23
        public DataTable ReturnPLBasTableConfList(string ParentAreaID, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT  *
FROM    ( SELECT    conf.CompanyID ,
                    conf.Yearv ,
                    conf.Weekv ,
                    conf.ParentAreaID ,
                    conf.ItemID ,
                    conf.Price ,
                    item.ItemName ,
                    mi.ModelID ,
                    mi.ModelName ,
                    conf.UpdateRate * 100 AS UpdateRate ,
                    conf.DevelopRate ,
                    dict.dictName AS ParentAreaName ,
                    ROW_NUMBER() OVER ( ORDER BY conf.ParentAreaID, conf.Yearv DESC, conf.Weekv DESC, conf.ItemID, conf.Price ) AS Row
          FROM     ngoods.ngoods_PL_Bas_TableConf  conf
                    INNER JOIN ndw.ndw_global_dictionary dict ON conf.ParentAreaID = dict.dictValue and  conf.ExtrSource = dict.ExtrSource and  conf.CompanyID = dict.CompanyID
                    INNER JOIN nods.nods_erp_SD_Mat_Item item ON conf.ItemID = item.ItemID and  conf.ExtrSource = item.ExtrSource
                    INNER JOIN ngoods.ngoods_PL_ModelItem mi ON item.ItemID = mi.ItemID and  item.ExtrSource = mi.ExtrSource
          WHERE     1 = 1 and  conf.ExtrSource = '{UserInfo.UserExtrSource}' and  conf.CompanyID = '{UserInfo.UserCompanyId}'
{(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and conf.ParentAreaID ='{ParentAreaID}' ")}
        ) table1
WHERE   Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回推台配置表总数
        /// </summary>
        /// <param name="keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-23
        public int ReturnPLBasTableConfCount(string ParentAreaID)
        {
            try
            {
                var sql = $@"select count(1)
from ngoods.ngoods_PL_Bas_TableConf  conf
                    INNER JOIN ndw.ndw_global_dictionary dict ON conf.ParentAreaID = dict.dictValue and  conf.ExtrSource = dict.ExtrSource and  conf.CompanyID = dict.CompanyID
                    INNER JOIN nods.nods_erp_SD_Mat_Item item ON conf.ItemID = item.ItemID and  conf.ExtrSource = item.ExtrSource
                    INNER JOIN ngoods.ngoods_PL_ModelItem mi ON item.ItemID = mi.ItemID and  item.ExtrSource = mi.ExtrSource
          WHERE     1 = 1 and  conf.ExtrSource = '{UserInfo.UserExtrSource}' and  conf.CompanyID = '{UserInfo.UserCompanyId}'
{(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and conf.ParentAreaID ='{ParentAreaID}' ")}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-23
        [JsonType]
        [MustLogin]
        [Description("新增推台配置表")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CrePLBasTableConf(PLBasTableConfModel.NeceModel param)
        {
            try
            {
                var sql = $@"INSERT INTO ngoods.ngoods_PL_Bas_TableConf
(ExtrSource,CompanyID, ParentAreaID, Yearv, Weekv, ItemID, Price, UpdateRate, DevelopRate,CheckState)
VALUES('{UserInfo.UserExtrSource}','{UserInfo.UserCompanyId}','{param.ParentAreaID}',{param.Yearv},{param.Weekv},'{param.ItemID}',{param.Price}, {param.UpdateRate / (decimal)100.0}, {param.DevelopRate},1)";
                var list = AllPLBasTableConf();
                if (
                    list.Any(
                        d =>
                            d.ParentAreaID == param.ParentAreaID && d.Yearv == param.Yearv && d.Weekv == param.Weekv &&
                            d.ItemID == param.ItemID)) return Error("该数据已存在！");
                var cmdList = new List<CommandInfo>() {
                    new CommandInfo() {
                        CommandText=sql,
                    },
                };
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回所有推台配置表
        /// </summary>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-31
        public IList<PLBasTableConfModel.NeceModel> AllPLBasTableConf()
        {
            try
            {
                var sql = @"select * from ngoods.ngoods_PL_Bas_TableConf";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLBasTableConfModel.NeceModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-23
        [JsonType]
        [MustLogin]
        [Description("修改推台配置表")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdatePLBasTableConf(PLBasTableConfModel.NeceModel param)
        {
            try
            {
                var sql = $@"UPDATE ngoods.ngoods_PL_Bas_TableConf
SET Price={param.Price}, UpdateRate={param.UpdateRate / (decimal)100.0}, DevelopRate={param.DevelopRate},CheckState=1
where ExtrSource = '{UserInfo.UserExtrSource}' and CompanyID ='{UserInfo.UserCompanyId}' and ParentAreaID='{param.ParentAreaID}' and Yearv={param.Yearv} and Weekv={param.Weekv} and ItemID='{param.ItemID}'";
                var cmdList = new List<CommandInfo>() {
                    new CommandInfo() {
                        CommandText=sql,
                    },
                };
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 枚举
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-10
        public void MergePLBasTableConf(IList<PLBasTableConfModel.NeceModel> list)
        {
            try
            {
                var uid = Guid.NewGuid().ToString().Replace("-", "");
                var tempTableName = $" ngoods.ngoods_{uid} ";
                const string targetTableName = @" ngoods.ngoods_PL_Bas_TableConf ";
                var dt =
                    DataTableHelper.LinqToDataTable(
                        list.DistinctBy(d => new { d.CompanyID, d.Yearv, d.Weekv, d.ParentAreaID, d.ItemID }));
                const string onConditions = @" targetTable.ExtrSource=sourceTable.ExtrSource
         and  targetTable.CompanyID=sourceTable.CompanyID
         AND targetTable.Yearv=sourceTable.Yearv
         AND targetTable.Weekv=sourceTable.Weekv
         AND targetTable.ParentAreaID=sourceTable.ParentAreaID
         AND targetTable.ItemID=sourceTable.ItemID ";
                const string setConditions = @" UpdateRate = sourceTable.UpdateRate/100 ,
                                            Price = sourceTable.Price ,
                                            DevelopRate = sourceTable.DevelopRate ,
                                            CheckState = sourceTable.CheckState ";
                const string insertConditions = @" ExtrSource ,
                      CompanyID ,
                      Yearv ,
                      Weekv ,
                      ParentAreaID ,
                      ItemID ,
                      UpdateRate ,
                      Price ,
                      DevelopRate ,
                      CheckState ";
                const string valuesConditions = @" sourceTable.ExtrSource ,
                      sourceTable.CompanyID ,
                      sourceTable.Yearv ,
                      sourceTable.Weekv ,
                      sourceTable.ParentAreaID ,
                      sourceTable.ItemID ,
                      sourceTable.UpdateRate/100 ,
                      sourceTable.Price ,
                      sourceTable.DevelopRate ,
                      sourceTable.CheckState ";
                Helper.Merge(tempTableName, targetTableName, dt, onConditions, setConditions, insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-10
        public void CreateTempTable(DataTable dt, string TempTableName)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    ExtrSource varchar(30) default '{UserInfo.UserExtrSource}',
    CompanyID varchar(30),
    Yearv int,
    Weekv int,
    ParentAreaName varchar(80),
    ItemName varchar(120),
    Price numeric(8,4),
    UpdateRate numeric(8,4),
    DevelopRate numeric(8,4)
)";
                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo
                    {
                        CommandText = sql,
                    }
                };
                Helper.ExecuteSqlTran(cmdList, dt, TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-10
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导入推台配置表
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-10
        public DataTable ExportPLBasTableConf(string TempTableName)
        {
            try
            {
                var sql = $@"SELECT  temp.ExtrSource ,
        temp.CompanyID ,
        temp.Yearv ,
        temp.Weekv ,
        temp.ParentAreaName ,
        temp.ItemName ,
        temp.Price ,
        temp.UpdateRate ,
        temp.DevelopRate ,
        dict.dictValue AS ParentAreaID ,
        item.ItemID
FROM    {TempTableName} temp
        LEFT JOIN ndw.ndw_global_dictionary dict ON temp.ParentAreaName = dict.dictName and  temp.ExtrSource = dict.ExtrSource and  temp.CompanyID = dict.CompanyID
        LEFT JOIN nods.nods_erp_SD_Mat_Item item ON temp.ItemName = item.ItemName  and  temp.ExtrSource = item.ExtrSource";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLBasTableConfModel.ListModel>.ConvertToModel(dt);
                var resultList =
                    list.Where(
                            d => d.ParentAreaID != null && d.ItemID != null && d.UpdateRate >= 0 && d.UpdateRate <= 100)
                        .Select(d => d)
                        .ToList();
                for (var index = resultList.Count; index > 0; index--)
                {
                    var item = resultList[index];
                    var maxWeek = CalendarHandler.GetYearWeekCount((int)item.Yearv);
                    if (!(item.Weekv <= 0) && !(item.Weekv > maxWeek)) continue;
                    resultList.Remove(item);
                }
                MergePLBasTableConf(resultList.Select(d => new PLBasTableConfModel.NeceModel
                {
                    CompanyID = d.CompanyID,
                    Yearv = d.Yearv,
                    Weekv = d.Weekv,
                    ParentAreaID = d.ParentAreaID,
                    ItemID = d.ItemID,
                    UpdateRate = d.UpdateRate,
                    Price = d.Price,
                    DevelopRate = d.DevelopRate,
                    CheckState = 1L,
                }).ToList());
                var errorList = list.Except(resultList);
                var errorDT = DataTableHelper.LinqToDataTable(errorList);
                if (errorDT.Rows.Count > 0)
                {
                    errorDT.Columns.Remove("ParentAreaID");
                    errorDT.Columns.Remove("ItemID");
                    errorDT.Columns.Remove("ModelID");
                    errorDT.Columns.Remove("ModelName");
                }
                return errorDT;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
